package com.fs.utils;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class JDBCUtils {


    public static String parseUpdateSQL(Object obj,String tableName){



        if(obj == null)
            throw  new RuntimeException("传入对象不能为null");


        Class<?> aClass = obj.getClass();

        Field id = null;
        StringBuffer sql = null;
        try {

            Connection connection = MyConnectionPool.getConnection();

            PreparedStatement prep = connection.prepareStatement("select * from "+tableName+" where false");

            ResultSetMetaData metaData = prep.executeQuery().getMetaData();

            connection.close();

            List<String> dataBaseField = new ArrayList<>();
            List<String> dataBaseFieldToLow = new ArrayList<>();

            for(int i = 1; i <= metaData.getColumnCount();i++){
                dataBaseField.add(metaData.getColumnName(i));
                dataBaseFieldToLow.add(metaData.getColumnName(i).replaceAll("_","").toLowerCase());
            }



            id = aClass.getDeclaredField("id");

            id.setAccessible(true);
            if(id == null  || id.get(obj) == null )
                throw  new RuntimeException("id不能为null");


            sql = new StringBuffer("update "+tableName+" set id=id ");

            for(Field filed:aClass.getDeclaredFields()){

                String filedName = filed.getName();

                filed.setAccessible(true);

                Object o = filed.get(obj);
                if( o != null && !filedName.equals("id") && !filedName.equals("serialVersionUID")){
                    String filedLow = filedName.toLowerCase();
                    if(dataBaseFieldToLow.contains(filedLow))
                        filedName = dataBaseField.get(dataBaseFieldToLow.indexOf(filedLow));

                    sql.append(","+filedName+"=?");
                }


            }

        } catch (Exception e) {
            e.printStackTrace();
        }



        return  sql.append(" where id = ?").toString();
    }


    /**
     * 通用查询方法
     * @param sql  SQL语句
     * @param entityClass 实体类类型
     * @param a    参数数组
     * @param <T>
     * @return   返回值是一个集合
     */
    public static <T> List<T> executeQurey(String sql, Class<T> entityClass,Object... a) {

        try {
            Connection connection = MyConnectionPool.getConnection();

            PreparedStatement prep = connection.prepareStatement(sql);


            if (a.length > 0 && a[0].getClass() == entityClass)//如果传入的参数是实体类
                ifParamEntity(sql, a[0], prep);
            else {

                for (int i = 0; i < a.length; ++i)
                    prep.setObject(i + 1, a[i]);
            }

            ResultSet resultSet = prep.executeQuery();

            List<T> ts = processResultSet(resultSet, entityClass);

            resultSet.close();
            prep.close();
            connection.close();

            return ts;

        } catch (Exception e) {
            e.printStackTrace();
        }


        return null;
    }

    /**
     * 通用修改方法
     * @param sql  SQL语句
     * @param a    参数数组
     * @return
     */
    public static int executeUpdate(String sql,Object... a){
        try {
            Connection connection = MyConnectionPool.getConnection();


            PreparedStatement prep = connection.prepareStatement(sql);


            if (!(a[0].getClass().isPrimitive() || a[0] instanceof Number || a[0] instanceof String))//如果传入的参数是实体类
                ifParamEntity(sql, a[0], prep);
            else {

                for (int i = 0; i < a.length; ++i)
                    prep.setObject(i + 1, a[i]);
            }

            int i = prep.executeUpdate();

            prep.close();
            connection.close();

            return i;

        } catch (Exception e) {
            e.printStackTrace();
        }


        return 0;
    }

    /**
     * 判断传入参数是为实体类
     * @param sql
     * @param entity
     * @param prep
     * @param <T>
     * @throws IllegalAccessException
     * @throws SQLException
     */
    private static <T> void ifParamEntity(String sql, T entity, PreparedStatement prep) throws IllegalAccessException, SQLException {
        //使用正则表达式获取sql中的字段

        Pattern compile = null;

        String testSql = sql.toLowerCase();

        if(sql.indexOf("insert") < 0 )
            compile = Pattern.compile("[']?(\\w+)[']?\\s*=\\s*\\?");
        else
            compile = Pattern.compile("[']?(\\w+)[']?[,)]+");

        Matcher matcher = compile.matcher(testSql);

        List<String> db_fields = new ArrayList<>();

        //数据库字段统一小写且去除下划线
        while (matcher.find())
            db_fields.add(matcher.group(1).replaceAll("_", "").trim());


        //数据库字段与实体类字段匹配，并赋值

        for (Field f : entity.getClass().getDeclaredFields())
            if (db_fields.contains(f.getName().toLowerCase())) {
                f.setAccessible(true);
                prep.setObject(db_fields.indexOf(f.getName().toLowerCase())+1, f.get(entity));
            }
    }

    /**
     * 处理结果集
     * @param rs
     * @param entityClass
     * @param <T>
     * @return
     * @throws SQLException
     * @throws IllegalAccessException
     * @throws InstantiationException
     */
    private static <T> List<T> processResultSet(ResultSet rs,Class<T> entityClass ) throws SQLException, IllegalAccessException, InstantiationException {

        List<T> list = null;

        Map<String,Integer> db_fields_index = new HashMap<>();

        while (rs != null && rs.next()){

            if(list ==null){
                list = new ArrayList<>();

                for(int i = 1 ; i <= rs.getMetaData().getColumnCount(); ++i)
                    db_fields_index.put(rs.getMetaData().getColumnName(i).replaceAll("_","").toLowerCase(),i);
            }

            T t = entityClass.newInstance();


            for(Field f : entityClass.getDeclaredFields())
                if(db_fields_index.containsKey(f.getName().toLowerCase())){
                    f.setAccessible(true);

                    Object object = rs.getObject(db_fields_index.get(f.getName().toLowerCase()));

                    if(object instanceof Date)
                        object = object.toString();
                    else if(object instanceof BigDecimal){

                        if(f.getType() == Integer.class)
                            object = ((BigDecimal) object).intValue();
                        else if(f.getType() == Float.class)
                            object = ((BigDecimal) object).floatValue();
                        else if( f.getType() == Double.class)
                            object = ((BigDecimal) object).doubleValue();

                    }


                    f.set(t,object);
                }

            list.add(t);
        }

        return list;
    }

}
